package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import util.DBUtil;
import util.Page;

import model.Course;
import model.TrainType;
import dao.CourseDAO;

public class CourseDAOImpl implements CourseDAO {

	public Page<Course> getAllCourse(int pageNo, int pageSize) {
		Page<Course> page = new Page<Course>(pageNo, pageSize);
		int totalCount = 0;
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<Course> list = new ArrayList<Course>();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from course";
			pstat=conn.prepareStatement(sql);
			rs=pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select * from course) s where rownum <=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, page.getPageNo()*pageSize);
			pstat.setInt(2,(page.getPageNo()-1)*pageSize+1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				Course  c = new Course();
				c.setId(rs.getInt("ID"));
				c.setName(rs.getString("NAME"));
				c.setCoursetime(rs.getDouble("COURSETIME"));
				list.add(c);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}

	public Page<Course> getCourseByName(int pageNo, int pageSize, String name) {
		Page<Course> page = new Page<Course>(pageNo, pageSize);
		int totalCount = 0;
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<Course> list = new ArrayList<Course>();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from course where name like'%"+name+"%'";
			pstat=conn.prepareStatement(sql);
			rs=pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select * from course where name like'%"+name+"%') s where rownum <=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, page.getPageNo()*pageSize);
			pstat.setInt(2,(page.getPageNo()-1)*pageSize+1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				Course  c = new Course();
				c.setId(rs.getInt("ID"));
				c.setName(rs.getString("NAME"));
				c.setCoursetime(rs.getDouble("COURSETIME"));
				list.add(c);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}

	public boolean addCourse(Course course) {
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		boolean flag=false;
		try {
			conn = dbu.getConnection();
			String sql = "insert into course(ID,NAME,COURSETIME) values(seq.nextval,?,?)";
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, course.getName());
			pstat.setDouble(2, course.getCoursetime());
			int i=pstat.executeUpdate();
			if(i>0)
			{
				flag=true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		
		return flag;
	}

	public boolean ifExists(String name) {
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		boolean flag=false;
		try {
			conn = dbu.getConnection();
			String sql = "select * from course where name=?";
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, name);
			rs = pstat.executeQuery();
			if(rs.next())
			{
				flag=true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		
		return flag;
	}

	public boolean deleteByCourse(Course course) {
		// TODO Auto-generated method stub
		return false;
	}

	public boolean deleteByCourseId(int id) {
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		boolean flag=false;
		try {
			conn = dbu.getConnection();
			String sql = "delete from course where id=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, id);
			rs = pstat.executeQuery();
			flag=true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return flag;
	}

	public Course getById(int id) {
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		Course  c = null;
		try {
			conn = dbu.getConnection();
			String sql = "select * from course where id=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, id);
			rs = pstat.executeQuery();
			while (rs.next()) {	
				c=new Course();
				c.setId(rs.getInt("ID"));
				c.setName(rs.getString("NAME"));
				c.setCoursetime(rs.getDouble("COURSETIME"));
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return c;
	}

	public boolean updateCourse(Course course) {
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		try {
			conn = dbu.getConnection();
			String sql = "update course set name=?,coursetime=? where id=?";
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, course.getName());
			pstat.setDouble(2, course.getCoursetime());
			pstat.setInt(3, course.getId());
			pstat.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return true;
	}

	public List<Course> getAll() {
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<Course> list=new ArrayList<Course>();
		try {
			conn = dbu.getConnection();
			String sql = "select * from course";
			pstat = conn.prepareStatement(sql);
			rs=pstat.executeQuery();
			while(rs.next())
			{
				Course c=new Course();
				c.setId(rs.getInt("ID"));
				c.setName(rs.getString("NAME"));
				c.setCoursetime(rs.getDouble("COURSETIME"));
				list.add(c);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return list;
	}
	public Page<Course> getByTime(int pageNo, int pageSize,double time)
	{
		Page<Course> page = new Page<Course>(pageNo, pageSize);
		int totalCount = 0;
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<Course> list = new ArrayList<Course>();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from course where COURSETIME=?";
			pstat=conn.prepareStatement(sql);
			pstat.setDouble(1, time);
			rs=pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select * from course where COURSETIME=?) s where rownum <=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setDouble(1, time);
			pstat.setInt(2, page.getPageNo()*pageSize);
			pstat.setInt(3,(page.getPageNo()-1)*pageSize+1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				Course  c = new Course();
				c.setId(rs.getInt("ID"));
				c.setName(rs.getString("NAME"));
				c.setCoursetime(rs.getDouble("COURSETIME"));
				list.add(c);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}
}
